﻿using System;
using Eassets.Layered.Domain.Asset;
using System.Data.SqlClient;
using System.Data;
using System.Collections.ObjectModel;

namespace Eassets.Data.Asset
{
    public class TransactionData
    {
        /// <summary>
        /// Inserts transaction.
        /// </summary>
        /// <param name="transaction"></param>
        public void AddTransaction(Transaction transaction)
        {
            SqlCommand command = new SqlCommand("SP_AddTransaction"); // Name of the SP
            CommonData commonData = new CommonData(); commonData.ExecuteNonQuery(ConstructCommand(command, transaction));

        }

        /// <summary>
        /// Updates transaction.
        /// </summary>
        /// <param name="transaction"></param>
        public void ModifyTransaction(Transaction transaction)
        {
            SqlCommand command = new SqlCommand("SP_ModifyTransaction"); // Name of the SP
            command.Parameters.AddWithValue("@TransactionId", transaction.TransactionId);
            command.Parameters.AddWithValue("@Status", transaction.TransactionStatus);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); commonData.ExecuteNonQuery(command);
        }

        /// <summary>
        /// Delete transaction.
        /// </summary>
        /// <param name="transactionId"></param>
        public void DeleteTransaction(long transactionId)
        {
            SqlCommand command = new SqlCommand("SP_DeleteTransaction"); // Name of the SP
            command.Parameters.AddWithValue("@TransactionId", transactionId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); commonData.ExecuteNonQuery(command);
        }

        public long GetMaxTransactionNumber()
        {
            long id = 0;
            SqlCommand command = new SqlCommand("SP_GetMaxTransactionNumber");

            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); SqlDataReader dataReader = commonData.ExecuteReader(command);
            while (dataReader.Read())
            {
                if (!(String.IsNullOrEmpty(dataReader["maxTranId"].ToString())))
                {
                    id = Convert.ToInt64(dataReader["maxTranId"]);
                }
            }
            return id;
        }

        /// <summary>
        /// Get transaction by id.
        /// </summary>
        /// <param name="transactionId"></param>
        /// <returns></returns>
        public Transaction GetByIdTransaction(long transactionId)
        {
            SqlCommand command = new SqlCommand("SP_GetTransactionById");
            command.Parameters.AddWithValue("@TransactionId", transactionId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); SqlDataReader dataReader = commonData.ExecuteReader(command);
            Transaction transaction = new Transaction();
            while (dataReader.Read())
            {
                if (!(String.IsNullOrEmpty(dataReader["TransactionId"].ToString())))
                {
                    transaction.TransactionId = Convert.ToInt64(dataReader["TransactionId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionTypeCode"].ToString())))
                {
                    transaction.TransactionTypeCode = Convert.ToString(dataReader["TransactionTypeCode"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionDocumentCode"].ToString())))
                {
                    transaction.TransactionDocumentCode = Convert.ToString(dataReader["TransactionDocumentCode"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["CompanyId"].ToString())))
                {
                    transaction.CompanyId = Convert.ToInt64(dataReader["CompanyId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["OrganizationLevelListId"].ToString())))
                {
                    transaction.OrganizationLevelListId = Convert.ToInt64(dataReader["OrganizationLevelListId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionNo"].ToString())))
                {
                    transaction.TransactionNo = Convert.ToString(dataReader["TransactionNo"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionUserId"].ToString())))
                {
                    transaction.TransactionUserId = Convert.ToInt64(dataReader["TransactionUserId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionStatus"].ToString())))
                {
                    transaction.TransactionStatus = Convert.ToString(dataReader["TransactionStatus"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionDate"].ToString())))
                {
                    transaction.TransactionDate = Convert.ToDateTime(dataReader["TransactionDate"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionConfirmDate"].ToString())))
                {
                    transaction.TransactionConfirmDate = Convert.ToDateTime(dataReader["TransactionConfirmDate"]);
                }
            }
            dataReader.Close();
            return transaction;
        }

        /// <summary>
        /// GetAll transaction.
        /// </summary>
        /// <returns></returns>
        public Collection<Transaction> GetAllTransaction()
        {
            Collection<Transaction> transactions = new Collection<Transaction>();
            SqlCommand command = new SqlCommand("SP_GetAllTransactions");
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); SqlDataReader dataReader = commonData.ExecuteReader(command);
            Transaction transaction;
            while (dataReader.Read())
            {
                transaction = new Transaction();
                if (!(String.IsNullOrEmpty(dataReader["TransactionId"].ToString())))
                {
                    transaction.TransactionId = Convert.ToInt64(dataReader["TransactionId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionTypeCode"].ToString())))
                {
                    transaction.TransactionTypeCode = Convert.ToString(dataReader["TransactionTypeCode"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionDocumentCode"].ToString())))
                {
                    transaction.TransactionDocumentCode = Convert.ToString(dataReader["TransactionDocumentCode"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["CompanyId"].ToString())))
                {
                    transaction.CompanyId = Convert.ToInt64(dataReader["CompanyId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["OrganizationLevelListId"].ToString())))
                {
                    transaction.OrganizationLevelListId = Convert.ToInt64(dataReader["OrganizationLevelListId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionNo"].ToString())))
                {
                    transaction.TransactionNo = Convert.ToString(dataReader["TransactionNo"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionUserId"].ToString())))
                {
                    transaction.TransactionUserId = Convert.ToInt64(dataReader["TransactionUserId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionStatus"].ToString())))
                {
                    transaction.TransactionStatus = Convert.ToString(dataReader["TransactionStatus"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionDate"].ToString())))
                {
                    transaction.TransactionDate = Convert.ToDateTime(dataReader["TransactionDate"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionConfirmDate"].ToString())))
                {
                    transaction.TransactionConfirmDate = Convert.ToDateTime(dataReader["TransactionConfirmDate"]);
                }

                transactions.Add(transaction);
            }
            dataReader.Close();
            return transactions;
        }

        public long GetMaxTransactionId()
        {
            long tranId = 0;
            SqlCommand command = new SqlCommand("SP_GetMaxTransactionNumber");

            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); SqlDataReader dataReader = commonData.ExecuteReader(command);
            while (dataReader.Read())
            {
                if (!(String.IsNullOrEmpty(dataReader["maxTranId"].ToString())))
                {
                    tranId = Convert.ToInt64(dataReader["maxTranId"]);
                }
            }
            return tranId;
        }

        public long GetTransactionIdByTransactionNo(string transactionNo)
        {
            long id = 0;
            SqlCommand command = new SqlCommand("SP_GetTransactionIdByTransactionNo");
            command.Parameters.AddWithValue("@TransactionNo", transactionNo);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); SqlDataReader dataReader = commonData.ExecuteReader(command);
            while (dataReader.Read())
            {
                if (!(String.IsNullOrEmpty((dataReader["TransactionId"].ToString()))))
                {
                    id = Convert.ToInt64(dataReader["TransactionId"]);
                }
            }
            return id;
        }

        public string GetTransactionStatusByAssetCode(string assetCode)
        {
            string status = "";
            SqlCommand command = new SqlCommand("SP_GetTransactionStatusByAssetCode");
            command.Parameters.AddWithValue("@AssetCode", assetCode);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); SqlDataReader dataReader = commonData.ExecuteReader(command);
            while (dataReader.Read())
            {
                if (!(String.IsNullOrEmpty((dataReader["TranStatus"].ToString()))))
                {
                    status = Convert.ToString(dataReader["TranStatus"]);
                }
            }
            return status;
        }

        public string GetEmployeeNameByTransactionId(long transactionId)
        {
            string EmployeeName = "";
            SqlCommand command = new SqlCommand("SP_GetEmployeeNameByTransactionId");
            command.Parameters.AddWithValue("@TransactionId", transactionId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            while (dataReader.Read())
            {
                if (!(String.IsNullOrEmpty((dataReader["EmployeeName"].ToString()))))
                {
                    EmployeeName = Convert.ToString(dataReader["EmployeeName"]);
                }
            }
            return EmployeeName;
        }

        public Collection<AssetProcurementDetails> GETAssetDetailsByTransactionId(long transactionId)
        {

            Collection<AssetProcurementDetails> assetProcurementDetails = new Collection<AssetProcurementDetails>();
            AssetProcurementDetails assetProcurementDetail;
            SqlCommand command = new SqlCommand("SP_GETAssetDetailsByTransactionId");
            command.Parameters.AddWithValue("@TransactionId", transactionId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            while (dataReader.Read())
            {
                assetProcurementDetail = new AssetProcurementDetails();


                //if (!(String.IsNullOrEmpty((dataReader["Description"].ToString()))))
                //{
                //    assetProcurementDetail. = Convert.ToString(dataReader["Description"]);
                //}

                if (!(String.IsNullOrEmpty((dataReader["AssetCode"].ToString()))))
                {
                    assetProcurementDetail.AssetCode = Convert.ToString(dataReader["AssetCode"]);
                }

                if (!(String.IsNullOrEmpty((dataReader["SerialQuantity"].ToString()))))
                {
                    assetProcurementDetail.SerialQuantity = Convert.ToInt16(dataReader["SerialQuantity"]);
                }
                if (!(String.IsNullOrEmpty((dataReader["UnitPrice"].ToString()))))
                {
                    assetProcurementDetail.UnitPrice = Convert.ToDecimal(dataReader["UnitPrice"]);
                }
                if (!(String.IsNullOrEmpty((dataReader["TotalPrice"].ToString()))))
                {
                    assetProcurementDetail.TotalPrice = Convert.ToDecimal(dataReader["TotalPrice"]);
                }

                assetProcurementDetails.Add(assetProcurementDetail);
            }
            dataReader.Close();
            return assetProcurementDetails;
        }

        public Collection<Transaction> GetAllTransactionByTransactionDocumentCode(string code)
        {
            SqlCommand command = new SqlCommand("SP_GetAllTransactionByTransactionDocumentCode");
            command.Parameters.AddWithValue("@TransactionDocumentCode", code);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            Collection<Transaction> transactions = new Collection<Transaction>();
            Transaction transaction;
            while (dataReader.Read())
            {
                transaction = new Transaction();
                if (!(String.IsNullOrEmpty(dataReader["TransactionId"].ToString())))
                {
                    transaction.TransactionId = Convert.ToInt64(dataReader["TransactionId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionTypeCode"].ToString())))
                {
                    transaction.TransactionTypeCode = Convert.ToString(dataReader["TransactionTypeCode"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionDocumentCode"].ToString())))
                {
                    transaction.TransactionDocumentCode = Convert.ToString(dataReader["TransactionDocumentCode"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["CompanyId"].ToString())))
                {
                    transaction.CompanyId = Convert.ToInt64(dataReader["CompanyId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["OrganizationLevelListId"].ToString())))
                {
                    transaction.OrganizationLevelListId = Convert.ToInt64(dataReader["OrganizationLevelListId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionNo"].ToString())))
                {
                    transaction.TransactionNo = Convert.ToString(dataReader["TransactionNo"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionUserId"].ToString())))
                {
                    transaction.TransactionUserId = Convert.ToInt64(dataReader["TransactionUserId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionStatus"].ToString())))
                {
                    transaction.TransactionStatus = Convert.ToString(dataReader["TransactionStatus"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionDate"].ToString())))
                {
                    transaction.TransactionDate = Convert.ToDateTime(dataReader["TransactionDate"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionConfirmDate"].ToString())))
                {
                    transaction.TransactionConfirmDate = Convert.ToDateTime(dataReader["TransactionConfirmDate"]);
                }

                transactions.Add(transaction);
            }
            dataReader.Close();
            return transactions;
        }

        public Collection<Transaction> GetTransactionNoByOrgLevelListId(long orgLevelListId)
        {
            Collection<Transaction> transactionList = new Collection<Transaction>();
            SqlCommand command = new SqlCommand("SP_GetTransactionNoByOrganizationLevelListId");
            command.Parameters.AddWithValue("@OrganizationLevelListId", orgLevelListId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            Transaction transaction;
            while (dataReader.Read())
            {
                transaction = new Transaction();
                if (!(String.IsNullOrEmpty((dataReader["TransactionNo"].ToString()))))
                {
                    transaction.TransactionNo = Convert.ToString(dataReader["TransactionNo"]);
                }
                transactionList.Add(transaction);
            }
            dataReader.Close();
            return transactionList;
        }

        public SqlCommand ConstructCommand(SqlCommand command, Transaction transaction)
        {
            command.CommandType = CommandType.StoredProcedure;


            try
            {
                command.Parameters.AddWithValue("@TransactionTypeCode", transaction.TransactionTypeCode);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@TransactionDocumentCode", transaction.TransactionDocumentCode);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@CompanyId", transaction.CompanyId);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@OrganizationLevelListId", transaction.OrganizationLevelListId);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@TransactionNo", transaction.TransactionNo);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@TransactionUserId", transaction.TransactionUserId);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@TransactionStatus", transaction.TransactionStatus);
            }
            catch (Exception)
            { }

            return command;
        }

    }
}

